// customers CRUD

var mysql = require('./db');

// <editor-fold defaultstate="collapsed" desc="read = function(req, res)">
exports.read = function(req, res) {

    var search = '';
    if(req.query.search) {
        search = 'WHERE cp.profile_name LIKE "%' + req.query.search + '%" ';
    }
    var sort = JSON.parse(req.query.sort)[0];

    mysql.query(
/*rows[0]*/ 'SELECT SQL_CALC_FOUND_ROWS cp.id id,cp.profile_name profile_name,cp.terms terms,cp.tax_rate tax_rate,' +
            'cp.b_company b_company,cp.b_attn b_attn,cp.b_street1 b_street1,cp.b_street2 b_street2,' +
            'cp.b_csz b_csz,cp.b_contact b_contact,cp.b_phone b_phone,cp.b_email b_email,' +
            'cp.s_company s_company,cp.s_attn s_attn,cp.s_street1 s_street1,cp.s_street2 s_street2,' +
            'cp.s_csz s_csz,cp.s_contact s_contact,cp.s_phone s_phone,cp.s_email s_email,' +
            'cp.ownerID ownerID,cp.deleted deleted,su.username username ' +
            'FROM exxact.customer_profiles cp ' +
            'LEFT JOIN system_users su ON (cp.ownerID=su.id) ' +
            search +
            'ORDER BY ' + sort.property + ' ' + sort.direction + ' LIMIT ' + req.query.start + ',' + req.query.limit + '; ' +
/*rows[1]*/ 'SELECT FOUND_ROWS() total;',
    function (err, rows) {
        if(err) {
            res.json({ success: false, crud: 'select customers', err: err });
        }
        else {
            res.json({ success: true, data: rows[0], total: eval(rows[1])[0].total });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="add = function(req, res)">
exports.add = function(req, res) {

    var param = req.body;

    mysql.query(
        'INSERT INTO exxact.customer_profiles SET ' +
        'profile_name=?,terms=?,tax_rate=?,' +
        'b_company=?,b_attn=?,b_street1=?,b_street2=?,b_csz=?,b_contact=?,b_phone=?,b_email=?,' +
        's_company=?,s_attn=?,s_street1=?,s_street2=?,s_csz=?,s_contact=?,s_phone=?,s_email=?,' +
        'ownerID=?,deleted=?',
        [
            param.profile_name, param.terms, param.tax_rate,
            param.b_company, param.b_attn, param.b_street1, param.b_street2, param.b_csz, param.b_contact, param.b_phone, param.b_email,
            param.s_company, param.s_attn, param.s_street1, param.s_street2, param.s_csz, param.s_contact, param.s_phone, param.s_email,
            param.ownerID, param.deleted
        ],
        function(err, result) {
        if(err) {
            res.json({ success: false, crud: 'insert customer', err: err });
        }
        else {
            result.id = result.insertId;
            res.json({ success: true, data: result });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="update = function(req, res)">
exports.update = function(req, res) {

    var param = req.body;
    var id = param.id;
    // delete extra column
    delete param.id;
    delete param.username;

    mysql.query('UPDATE exxact.customer_profiles SET ? WHERE id=?', [param, id], function(err, result) {
        if(err) {
            res.json({ success: false, crud: 'update customer', err: err });
        }
        else {
            res.json({ success: true, data: result });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="destroy = function(req, res)">
exports.destroy = function(req, res) {

    var param = req.body;

    mysql.query('DELETE FROM exxact.customer_profiles WHERE id=?', param.id, function(err, result) {
        if(err) {
            res.json({ success: false, crud: 'delete', err: err });
        }
        else {
            res.json({ success: true, data: result });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="userswidgetread = function(req, res)">
exports.userswidgetread = function(req, res) {

    var search = '';
    if(req.query.search) {
        search = 'username LIKE "%' + req.query.search + '%" ';
    }

    mysql.query(
/*rows[0]*/ 'SELECT SQL_CALC_FOUND_ROWS id,username FROM exxact.system_users ' + 
            'WHERE deleted=0 AND ' + search +
            'LIMIT ' + req.query.start + ',' + req.query.limit + ';' +
/*rows[1]*/ 'SELECT FOUND_ROWS() total;',
    function (err, rows) {
        if(err) {
            res.json({ success: false, crud: 'widgetselect', err: err });
        }
        else {
            res.json({ success: true, data: rows[0], total: eval(rows[1])[0].total });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="CREATE TABLE exxact.customer_profiles">
/*
CREATE TABLE exxact.customer_profiles (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `profile_name` varchar(64) NOT NULL,
  `terms` varchar(32) NOT NULL,
  `tax_rate` double(10,2) NOT NULL DEFAULT '0.00',
  `b_company` text NOT NULL,
  `b_attn` text NOT NULL,
  `b_street1` text NOT NULL,
  `b_street2` text NOT NULL,
  `b_csz` text NOT NULL,
  `b_contact` text NOT NULL,
  `b_phone` text NOT NULL,
  `b_email` text NOT NULL,
  `s_company` text NOT NULL,
  `s_attn` text NOT NULL,
  `s_street1` text NOT NULL,
  `s_street2` text NOT NULL,
  `s_csz` text NOT NULL,
  `s_contact` text NOT NULL,
  `s_phone` text NOT NULL,
  `s_email` text NOT NULL,
  `ownerID` int(11) NOT NULL DEFAULT '0',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2050 DEFAULT CHARSET=latin1
*/

// change table
// ALTER TABLE customer_profiles MODIFY COLUMN profile_name VARCHAR(64) NOT NULL DEFAULT '';
// ALTER TABLE customer_profiles ADD INDEX profile_name(profile_name);
// ALTER TABLE customer_profiles MODIFY COLUMN terms VARCHAR(32) NOT NULL DEFAULT '';
// ALTER TABLE customer_profiles MODIFY COLUMN b_company VARCHAR(64) NOT NULL DEFAULT '';
// ALTER TABLE customer_profiles MODIFY COLUMN b_attn VARCHAR(64) NOT NULL DEFAULT '';
// ALTER TABLE customer_profiles MODIFY COLUMN b_street1 VARCHAR(64) NOT NULL DEFAULT '';
// ALTER TABLE customer_profiles MODIFY COLUMN b_csz VARCHAR(64) NOT NULL DEFAULT '';
// ALTER TABLE customer_profiles MODIFY COLUMN b_contact VARCHAR(64) NOT NULL DEFAULT '';
// ALTER TABLE customer_profiles MODIFY COLUMN b_phone VARCHAR(64) NOT NULL DEFAULT '';
// ALTER TABLE customer_profiles MODIFY COLUMN b_email VARCHAR(64) NOT NULL DEFAULT '';
// ALTER TABLE customer_profiles ADD INDEX ownerID(ownerID);
/*
CREATE TABLE exxact.customer_profiles (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `profile_name` varchar(64) NOT NULL DEFAULT '',
  `terms` varchar(32) NOT NULL DEFAULT '',
  `tax_rate` double(10,2) NOT NULL DEFAULT '0.00',
  `b_company` varchar(64) NOT NULL DEFAULT '',
  `b_attn` varchar(64) NOT NULL DEFAULT '',
  `b_street1` varchar(64) NOT NULL DEFAULT '',
  `b_street2` varchar(64) NOT NULL DEFAULT '',
  `b_csz` varchar(64) NOT NULL DEFAULT '',
  `b_contact` varchar(64) NOT NULL DEFAULT '',
  `b_phone` varchar(64) NOT NULL DEFAULT '',
  `b_email` varchar(64) NOT NULL DEFAULT '',
  `s_company` varchar(64) NOT NULL DEFAULT '',
  `s_attn` varchar(64) NOT NULL DEFAULT '',
  `s_street1` varchar(64) NOT NULL DEFAULT '',
  `s_street2` varchar(64) NOT NULL DEFAULT '',
  `s_csz` varchar(64) NOT NULL DEFAULT '',
  `s_contact` varchar(64) NOT NULL DEFAULT '',
  `s_phone` varchar(64) NOT NULL DEFAULT '',
  `s_email` varchar(64) NOT NULL DEFAULT '',
  `ownerID` int(11) NOT NULL DEFAULT '0',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `profile_name` (`profile_name`),
  KEY `ownerID` (`ownerID`)
) ENGINE=MyISAM AUTO_INCREMENT=2052 DEFAULT CHARSET=latin1
 */
// </editor-fold>
